/**
 * Copyright (C), 2020, JieYi Software System Co., Ltd.
 * All rights reserved.
 * FileName:       AbstractDbExecutor.java
 *
 * @Description:
 * @author: feiwe
 * Modification History:
 * Date         Author        Version        Discription
 * -------------------------------------------------------
 * 2020/11/9      feiwe                         add
 */


package com.fengwei.executor;

import com.fengwei.bean.DataSourcePoolManager;
import com.fengwei.bean.Page;
import com.fengwei.util.ExecutorConsts;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public abstract class AbstractDbExecutor {

    private final static Logger logger = LoggerFactory.getLogger(AbstractDbExecutor.class);

    public abstract String genUrl(String databaseType, String databaseHost, Integer databasePort, String databaseDbname);

    public void testConnection(String dbType, String url, String userName, String passWord) throws Exception {
        String driver = "";
        if (ExecutorConsts.DBTYPE_MYSQL.equals(dbType)) {
            driver = DataSourcePoolManager.MYSQL_DRIVER;
        } else if (ExecutorConsts.DBTYPE_ORACLE.equals(dbType)) {
            driver = DataSourcePoolManager.ORACLE_DRIVER;
        } else {
            throw new Exception("unknow dbType");
        }
        Class.forName(driver);
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, userName, passWord);
        } catch (Exception e) {
            throw e;
        } finally {
            if (null != connection) {
                connection.close();
            }
        }

    }

    public abstract Connection getConnection(String dbType, String url, String userName, String passWord) throws ClassNotFoundException,
            SQLException;

    public abstract Page<Map<String, Object>> executeSelectSql(Page<Map<String, Object>> page, Connection connect, String sql) throws Exception;

    public long executeInsertUpdateDeleteSql(Connection connection, String sql, int datachangeThreshold) throws Exception {
        Statement stmt = null;
        try {
            //不要自动提交
            connection.setAutoCommit(false);
            stmt = connection.createStatement();
            int count = stmt.executeUpdate(sql);
            if (count > datachangeThreshold) {
                connection.rollback();
                throw new Exception("影响超过" + datachangeThreshold + "条数据无法执行，请提交数据变更申请");
            } else {
                connection.commit();
            }
            return count;
        } catch (SQLException e) {
            connection.rollback();
            logger.error(e.getMessage(), e);
            throw new Exception(e.getMessage());
        } finally {
            stmt.close();
            //结束后自动提交
            connection.setAutoCommit(true);
        }
    }

    public void executeInsertUpdateDeleteSqlAfterCheck(Connection connection, List<String> sqlList, int applyNum) throws Exception {
        int count = 0;
        try {
            //不要自动提交
            connection.setAutoCommit(false);
            Statement stmt = null;
            for (String sql : sqlList) {
                try {
                    logger.debug("sql:" + sql);
                    stmt = connection.createStatement();
                    int countOne = stmt.executeUpdate(sql);
                    logger.debug("countOne:" + countOne);
                    count += countOne;
                } catch (SQLException e) {
                    connection.rollback();
                    throw e;
                } finally {
                    stmt.close();
                }
            }

            if (count == applyNum) {
                connection.commit();
            } else {
                connection.rollback();
                throw new Exception("实际执行SQL行数" + count + "和填写的影响行数" + applyNum + "不一致，执行失败");
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
            throw new Exception(e.getMessage());
        } finally {
            //结束后自动提交
            connection.setAutoCommit(true);
        }
    }

    public void closeConnection(Connection connect) throws SQLException {
        if (connect != null) {
            connect.close();
        }
    }

    public abstract List getAllTables(Connection connection, String name) throws Exception;

    public abstract String ddl(Connection connection, String tableName, String userName) throws Exception;

    protected List<Map<String, Object>> queryForList(Connection connect, String finalSql)
            throws Exception {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ResultSetMetaData rsmd = null;
        int maxSize = -1;
        String[] fields = null;
        List<String> times = new ArrayList<>();
        List<String> clob = new ArrayList<>();
        List<String> binary = new ArrayList<>();
        List<String> longs = new ArrayList<>();
        List<Map<String, Object>> rows = new ArrayList<>();
        try {
            pstmt = connect.prepareStatement(finalSql);
            rs = pstmt.executeQuery();
            rsmd = rs.getMetaData();
            maxSize = rsmd.getColumnCount();
            fields = new String[maxSize];
            for (int i = 0; i < maxSize; i++) {
                fields[i] = rsmd.getColumnLabel(i + 1);
                if (("java.sql.Timestamp".equals(rsmd.getColumnClassName(i + 1))) || ("oracle.sql.TIMESTAMP".equals(rsmd.getColumnClassName(i + 1)))) {
                    times.add(fields[i]);
                }
                if (("oracle.jdbc.OracleClob".equals(rsmd.getColumnClassName(i + 1))) || ("oracle.jdbc.OracleBlob".equals(rsmd.getColumnClassName(i + 1)))) {
                    clob.add(fields[i]);
                }
                if ("[B".equals(rsmd.getColumnClassName(i + 1))) {
                    binary.add(fields[i]);
                }
                if ("java.lang.Long".equals(rsmd.getColumnClassName(i + 1))) {
                    longs.add(fields[i]);
                }
            }

            Map<String, Object> row = null;
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            while (rs.next()) {
                row = new LinkedHashMap<>();
                for (int i = 0; i < maxSize; i++) {
                    Object value = times.contains(fields[i]) ? rs.getTimestamp(fields[i]) : rs.getObject(fields[i]);
                    if ((times.contains(fields[i])) && (value != null)) {
                        value = sdf.format(value);
                    }
                    if ((clob.contains(fields[i])) && (value != null)) {
                        value = "(Blob)";
                    }
                    if ((binary.contains(fields[i])) && (value != null)) {
                        value = new String((byte[]) value);
                    }
                    if ((longs.contains(fields[i])) && (value != null)) {
                        value = value + "";
                    }
                    row.put(fields[i], value);
                }
                rows.add(row);
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            rs.close();
            pstmt.close();
        }

        return rows;
    }

    protected int executeQueryForCount(Connection connect, String sql) {
        String finalSql = "select count(1) from (" + sql + ")";
        int rowCount = 0;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = connect.createStatement();
            rs = stmt.executeQuery(finalSql);
            rs.next();
            rowCount = rs.getInt(1);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            try {
                rs.close();
                stmt.close();
                //conn.close();
            } catch (SQLException localSQLException1) {
                logger.error(localSQLException1.getMessage(), localSQLException1);
            }
        }
        return rowCount;
    }


    protected int executeQueryForCount2(Connection connect, String sql) {
        int rowCount = 0;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery(sql);
            rs.last();
            rowCount = rs.getRow();
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            try {
                rs.close();
                stmt.close();
                //conn.close();
            } catch (SQLException localSQLException1) {
                logger.error(localSQLException1.getMessage(), localSQLException1);
            }
        }
        return rowCount;
    }

    protected List<Map<String, Object>> executeSqlForColumns(Connection connect, String sql) {
        String finalSql = " select * from  ( " + sql + " ) tab  limit 1 ";
        if ((sql.indexOf("show") == 0) || (sql.indexOf("SHOW") == 0)) {
            finalSql = sql;
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ResultSetMetaData rsmd = null;
        int maxSize = -1;
        List<Map<String, Object>> rows = new ArrayList<>();
        try {
            pstmt = connect.prepareStatement(sql);
            rs = pstmt.executeQuery();
            rsmd = rs.getMetaData();
            maxSize = rsmd.getColumnCount();
            for (int i = 0; i < maxSize; i++) {
                Map<String, Object> map = new HashMap<>();
                map.put("column_name", rsmd.getColumnLabel(i + 1));
                map.put("data_type", rsmd.getColumnTypeName(i + 1));
                rows.add(map);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                rs.close();
                pstmt.close();
                //conn.close();
            } catch (Exception e) {
                logger.error(e.getMessage(), e);
            }
        }
        return rows;
    }

}
